﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Reflection;
using System.Diagnostics;

namespace CinemaDbLib
{
    public class DBConnection
    {
        private Connection connection;

        private DBConnection()
        {
            connection = Connection.GetInstance();
            connection.Open();
        }

        private static DBConnection instance = null;
        public static DBConnection getInstance()
        {
            if (instance == null)
            {
                instance = new DBConnection();
            }

            return instance;
        }

        // private methods
        private Object createObject(Type objectType, DBObjectReflector reflector, SqlDataReader sqlData)
        {
            Object target = Activator.CreateInstance(objectType);
            foreach (PropertyInfo prop in objectType.GetProperties())
            {
                int dbPropIndex = sqlData.GetSchemaTable().Columns.IndexOf(prop.Name);
                if (dbPropIndex == -1) continue;
                Object value = sqlData.GetValue(dbPropIndex);
                reflector.setProperty(prop.Name, target, value);
            }

            return target;
        }

        private string createSetEnum(Object target, DBObjectReflector reflector)
        {
            string Enum = "";
            foreach (PropertyInfo prop in target.GetType().GetProperties())
            {
                // Skips the id field from set queries
                if (prop.Name == "id") continue;
                if (Enum != "") { Enum += ", "; }
                Object value = reflector.getProperty(prop.Name, target);
                Enum += prop.Name + " = '" + value.ToString() + "'";
            }

            return Enum;
        }

        // readers
        public Object readObject(Type objectType, DBObjectReflector reflector, string tableName, int id)
        {
            //Logger.getInstance().LogMessage("DBConnection -> readObject( Table => " + tableName + ", id = " + id.ToString() + " )");
            SqlDataReader sqlData = connection.Read("SELECT * FROM " + tableName + " WHERE id = " + id.ToString());
            if (sqlData.Read())
            {
                Object target = createObject(objectType, reflector, sqlData);
                return target;
            }
            else
            {
                return null;
            }
        }

        public List<Object> readMultipleObjects(Type objectType, DBObjectReflector reflector, string tableName, string condition)
        {
            SqlDataReader sqlData = connection.Read("SELECT * FROM " + tableName + " WHERE " + condition);
            List<Object> objects = new List<Object>();

            while (sqlData.Read())
            {
                Object target = createObject(objectType, reflector, sqlData);
                objects.Add(target);
            }

            return objects;
        }

        // save
        public bool saveObject(Object target, DBObjectReflector reflector, string tableName)
        {
            string Enum = createSetEnum(target, reflector);
            int targetId = (int)reflector.getProperty("id", target);
            int affectedRows = connection.Execute("UPDATE " + tableName + " SET " + Enum + " WHERE id = " + targetId.ToString());

            return affectedRows == 1;
        }

        // insert *** not sure it works 100% - must test
        public Object insertObject(Object target, DBObjectReflector reflector, string tableName)
        {
            string Enum = createSetEnum(target, reflector);
            Object result = connection.ExecuteScalar("INSERT INTO " + tableName + " SET " + Enum + " OUTPUT INSERTED.id");
            if (result != null)
            {
                reflector.setProperty("id", target, result);
                return target;
            }

            return null;
        }

        // delete
        public bool deleteObject(Object target, DBObjectReflector reflector, string tableName)
        {
            int targetId = (int)reflector.getProperty("id", target);
            int affectedRows = connection.Execute("DELETE FROM " + tableName + " WHERE id = " + targetId.ToString());

            return affectedRows == 1;
        }
    }
}
